-- @owner: zou_jialiang050
-- @date: 2024/4/2
-- @testpoint: pipelined函数返回嵌套表类型，其中嵌套嵌套表类型，合理报错

--step1:创建嵌套表类型，其中嵌套嵌套表类型;expect:合理报错
create or replace type typ_pipelined_0009 as(c1 integer, c2 float(10), c3 text, c4 date, c5 time);
create or replace type tb_type_0009 as table of typ_pipelined_0009;
create or replace type tb_type_0009_1 as table of tb_type_0009;

--step2:创建管道函数并查看返回值;expect:合理报错
create or replace function func_pipelined_0008(count in number)
returns tb_type_0008 pipelined
as $$
declare
temp_assoc associative_array_0008;
temp_type type_0008;
begin
for i in 1 .. count loop
temp_assoc.key := 'key' || i::TEXT;
temp_assoc.value := 'value' || i::TEXT;
temp_type.c1 := temp_assoc;
PIPE ROW(temp_type);
temp_assoc.key := '';
temp_assoc.value := '';
temp_type.c1 := temp_assoc;
PIPE ROW(temp_type);
temp_assoc.key := null;
temp_assoc.value := null;
PIPE ROW(temp_type);
end loop;
return;
end;
$$ language plpgsql;
/

--step3:清理环境;expect:成功
drop function if exists func_pipelined_0008_1;
drop type if exists tb_type_0008 cascade;
drop type if exists type_0008 cascade;
drop type if exists associative_array_0008 cascade;
